﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;

namespace WordService.Utility
{
    public class ExcelUtil
    {
        public static Microsoft.Office.Interop.Excel.Application ExportExcel(
            System.Data.DataTable dt,String filePrefix,ref String strPath)
        {
            if (dt == null)
                return null;
            Microsoft.Office.Interop.Excel.Application xlApp
                = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
                return null;
            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; // 取得第一个Sheet
            Range range = null;
            long totalCount = dt.Rows.Count;
            // 写入标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Range)worksheet.Cells[1, i + 1];
                range.Font.Bold = true;
                range.Font.Size = 9;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                // 加边框
                range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);
                range.EntireColumn.AutoFit();
                range.EntireRow.AutoFit();
            }
            // 写入内容
            for (int row = 0; row < dt.Rows.Count; row++)
            {
                for (int column = 0; column < dt.Columns.Count; column++)
                {
                    worksheet.Cells[row + 2, column + 1] = dt.Rows[row][column];
                    range = (Range)worksheet.Cells[row + 2, column + 1];
                    range.Font.Size = 9;

                    range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);
                    range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    range.EntireColumn.AutoFit();
                }
            }
            // 保存内容
            try
            {
                workbook.Saved = true;
                DateTime timeStamp = DateTime.Now;
                String filename = filePrefix + "_"
                    + timeStamp.Year + "_"
                    + timeStamp.Month + "_"
                    + timeStamp.Day + "_"
                    + timeStamp.Hour + "_"
                    + timeStamp.Minute + "_"
                    + timeStamp.Second + ".xls";
                workbook.SaveCopyAs(strPath + @"\" + filename);
                // 修改引用传递的Path内容
                strPath = strPath + @"\" + filename;
            }
            catch (Exception ex)
            {
                
            }
            return xlApp;
        }
    }
}
